import datetime
import os.path
from datetime import timezone
import requests
import time
import pandas as pd
from utils import df_into_db, read_sql


def timestamp_to_utc_str(timestamp, fmt="%Y-%m-%d %H:%M:%S"):
    """
    将时间戳转换为UTC时间字符串

    参数:
        timestamp: Unix时间戳（秒级）=]
        fmt: 输出字符串的格式
    返回:
        str: UTC时间字符串
    """
    # 创建UTC时间对象
    utc_dt = datetime.datetime.fromtimestamp(timestamp, tz=timezone.utc)

    # 格式化为字符串
    return utc_dt.strftime(fmt)


def utc_str_to_timestamp(str_time):
    # like"2013-01-01 00:00:00"
    dt = datetime.datetime.strptime(str_time, "%Y-%m-%d %H:%M:%S")
    utc_dt = dt.replace(tzinfo=timezone.utc)
    return int(utc_dt.timestamp())


def get_kline_from_binance_with_vol(symbol, end_time, frequency="1h"):
    print(symbol)
    assert frequency in ["1h", "5m", "1d"]
    start_time = 1451606400 * 1000  # utc时间: 2016-01-01 00:00:00
    all_datas = []
    while True:
        print(datetime.datetime.fromtimestamp(start_time / 1000))
        limit = 1000   # binance一次性最多可以拿1000条
        try:
            url = f"https://api.binance.com/api/v3/klines?symbol={symbol}&interval={frequency}&startTime={start_time}&limit={limit}"
            response = requests.get(url)
        except Exception as e:
            print(e)
            time.sleep(10)
            continue
        if response.status_code != 200:
            continue
        data = response.json()
        data = [x[:6] + x[7:11] for x in data]
        start_time = data[-1][0]
        all_datas.extend(data)
        if len(data) < 1000:
            break
    all_df = pd.DataFrame(all_datas, columns=["datetime", "open", "high", "low", "close", "vol", "amount",
                                              "number_of_trades", "active_vol", "active_amount"])
    all_df.rename(columns={"datetime": "timestamp"}, inplace=True)
    all_df["datetime"] = all_df["timestamp"].apply(lambda x: timestamp_to_utc_str(x/1000))
    all_df.drop_duplicates(subset=["timestamp"], keep="last", inplace=True)
    all_df["symbol"] = symbol
    all_df["datasource"] = "binance"
    all_df["frequency"] = frequency
    all_df["type"] = "spot"
    all_df = all_df[all_df["timestamp"] <= end_time]
    df_into_db(all_df, db_name="all_history_ohlcvm_coinmarketcap", table_name="binance_k_line")


def main():
    # symbols = ["ETHUSDT", "ETHBTC", "BNBUSDT", "BNBBTC", "XRPUSDT", "XRPBTC", "SOLUSDT", "TRXUSDT", "DOGEUSDT"]
    symbols = ["SOLBTC", "TRXBTC", "DOGEBTC"]
    before_yesterday = datetime.date.today()-datetime.timedelta(days=2)
    end_timestamp = utc_str_to_timestamp(before_yesterday.strftime('%Y-%m-%d %H:%M:%S'))*1000
    for symbol in symbols:
        get_kline_from_binance_with_vol(symbol, end_timestamp, frequency="1d")
        get_kline_from_binance_with_vol(symbol, end_timestamp, frequency="1h")


def save_to_file():
    # symbols = ["ETHUSDT", "ETHBTC", "BNBUSDT", "BNBBTC", "XRPUSDT", "XRPBTC", "SOLUSDT", "TRXUSDT", "DOGEUSDT"]
    symbols = ["SOLBTC", "TRXBTC", "DOGEBTC"]
    path = os.path.dirname(os.path.dirname(__file__))
    df = read_sql("select `datetime`, `symbol`,`open`, `high`, `low`, `close`, `vol`,`frequency` from binance_k_line",
                  db_name="all_history_ohlcvm_coinmarketcap")
    for symbol in symbols:
        for frequency in ["1h", "1d"]:
            symbol_df = df[(df["symbol"] == symbol) & (df["frequency"] == frequency)]
            print(f"symbol: {symbol}, frequency: {frequency},数据长度:{len(symbol_df)}, "
                  f"开始时间：{symbol_df['datetime'].min()}, 结束时间{symbol_df['datetime'].max()}")
            columns = ["datetime", "symbol", "open", "high", "low", "close", "vol"]
            symbol_df[columns].to_csv(f"{path}/kline/binance_{symbol}_kline_{frequency}.csv", index=False)


if __name__ == "__main__":
    main()
